import json
import time
import time
import json

import pymysql

# from pyxxl import ExecutorConfig, PyxxlRunner
# from pyxxl.ctx import g
from xxlOwner import owner
from xxlSetting import setting

# config = ExecutorConfig(
#     xxl_admin_baseurl="http://localhost:8080/xxl-job-admin/api/",
#     executor_app_name="python-house",
#     executor_host="127.0.0.1",
#     access_token="default_token"
# )

# app = PyxxlRunner(config)

# 拼接查询sql


def search():
    param = setting.executorParams
    print("get executor params: %s" % param)

    if param is None:
        return

    houseParam = json.loads(param)
    batchId = str(int(time.time()))

    sqlStr = ""
    for i in range(len(houseParam["section"])):
        section = houseParam["section"][i]
        max_val = houseParam["max"][i]

        sqlStr += "("
        sqlStr += f"select house_id, {batchId} as batch_id from house_follow where (house_id in ("
        sqlStr += f"SELECT house_id FROM house h where (is_del = 0) and (`section` = '{section}'))) "
        sqlStr += contentStr(houseParam["filter"])
        sqlStr += f" limit {max_val}"
        sqlStr += ")"

        sqlStr += " union all "

    sqlStr = sqlStr[:-11]  # Remove the last "union all" from the string
    houseParam["batchId"] = batchId

    if houseParam["isTest"]:
        print("拼接sql为:  " + sqlStr)
    else:
        print("执行的sql为:  " + sqlStr)

        # 执行 python 文件
        peocessSql(houseParam, sqlStr)

# 拼接in语句


def contentStr(contents):
    if not contents:
        return ""

    sqlStr = "AND ("
    for content in contents:
        sqlStr += f"(content like '%{content}%') or "

    sqlStr = sqlStr[:-4]  # Remove the last "or" from the string
    sqlStr += ")"
    return sqlStr

# 查询传递过来的sql


def fetch_data(sql):
    conn = pymysql.connect(
        host="43.156.247.50",  # 替换为实际的主机名
        user="root",  # 替换为实际的用户名
        password="520777",  # 替换为实际的密码
        database="house"  # 替换为实际的数据库名
    )
    cursor = conn.cursor()
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data

# 更新数据


def update_data(batch_id, row):
    try:
        house_id = row[0]
        conn = pymysql.connect(
            host="43.156.247.50",  # 替换为实际的主机名
            user="root",  # 替换为实际的用户名
            password="520777",  # 替换为实际的密码
            database="house"  # 替换为实际的数据库名
        )
        cursor = conn.cursor()

        try:
            # 构建插入数据的 SQL 查询
            sql = "INSERT INTO batch_house_owner (batch_id, house_id) VALUE (%s, %s);"
            values = (
                batch_id,
                house_id
            )

            # 执行 SQL 查询
            cursor.execute(sql, values)
            conn.commit()
        except Exception as e:
            g.logger.error(e)

        cursor.close()
        conn.close()
    except Exception as e:
        g.logger.error(e)


def batchInsert(batch_id, rows):
    try:
        conn = pymysql.connect(
            host="43.156.247.50",  # 替换为实际的主机名
            user="root",  # 替换为实际的用户名
            password="520777",  # 替换为实际的密码
            database="house"  # 替换为实际的数据库名
        )
        cursor = conn.cursor()

        try:
            # 构建插入数据的 SQL 查询
            for row in rows:
                sql = "INSERT INTO batch_house_owner (batch_id, house_id) VALUE (%s, %s);"
                values = (batch_id, row[0])
                cursor.execute(sql, values)

            # 统一提交
            conn.commit()
        except Exception as e:
            conn.rollback()
            g.logger.error(e)

        cursor.close()
        conn.close()
    except Exception as e:
        g.logger.error(e)

# 查询全量数据并打印


def select_house(batch_id):
    sql = f"select	house_id ,	`section` as 小区,	CONCAT(block_no, '栋', unit_no, '单元', cell, '室') as 房号,	price as 总价,	unit_price as 单价,	area as 建筑面积,	CONCAT(room , '室', hall, '厅') as 房间,	floor as 楼层,	total_floor as 总楼层,	decoration as 装修 from	house where	house_id in (	select		house_id	from		batch_house_owner	where		batch_id = {
        batch_id});"
    print("查询sql:" + sql)
    conn = pymysql.connect(
        host="43.156.247.50",  # 替换为实际的主机名
        user="root",  # 替换为实际的用户名
        password="520777",  # 替换为实际的密码
        database="house"  # 替换为实际的数据库名
    )
    cursor = conn.cursor()
    cursor.execute(sql)
    data = cursor.fetchall()
    cursor.close()
    conn.close()
    return data

# 执行sql语句


def peocessSql(houseParam, sqlStr):
    data = fetch_data(sqlStr)
    batch_id = str(int(houseParam["batchId"]))

    batchInsert(batch_id, data)

    print("执行完成: batch_id=" + batch_id)

    houses = select_house(batch_id)
    if len(houses) > 0:
        # 批量爬取，并入库
        if houseParam["isTest"] == False:
            owner.batchInsertOwner(houseParam, houses)

        # 入库成功并打印
        for house in houses:
            print(house)


# 抓取房产信息
# @app.handler.register(name="houseHandler")
# async def houseHandler():
#     try:
#         search()
#     except Exception as e:
#         g.logger.error(e)
#     return "成功..."


# app.run_executor()

# 主函数
if __name__ == '__main__':
    search()
